using System;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
using System.Collections.Generic;
using TienTeTuong;
using log4net;

namespace TienTeTuong
{
	/// <summary>
	/// Data access layer class for Category
	/// </summary>
	class CategoryDAO 
	{
        /// <summary>
        /// Get list category to create Menu
        /// </summary>
        /// <returns></returns>
        public List<CategoryDTO> GetListCategory()
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;
            var lstCategory = new List<CategoryDTO>();
            try
            {
              
                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand {Connection = SqlConnection};

                const string strSQL = @"SELECT * FROM category ORDER BY CreatedDate DESC";
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    try
                    {
                        var cate = new CategoryDTO
                                       {
                                           CateID = reader[0].ToString(),
                                           CateName = reader[1].ToString(),
                                           CateParent = reader[2].ToString(),
                                           Description = reader[3].ToString(),
                                           VipLevel = int.Parse(reader[4].ToString()),
                                           Active = reader[5].ToString()=="1",
                                           Price = (double) reader[6],
                                           Image = reader[7].ToString(),
                                           Status = (int) reader[8],
                                           CreatedDate = Convert.ToDateTime(reader[9]),
                                           UpdatedDate = Convert.ToDateTime(reader[10])
                                       };
                        lstCategory.Add(cate);

                    }
                    catch (Exception ex)
                    {
                       
                    }
                   

                }
                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return lstCategory;

            }
            catch (Exception ex)
            {
                if (reader != null) reader.Close();
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return lstCategory;
            }
        }
        public string GetCateNameByID(string CateID)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;
            string cateName="";
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                string strSQL = @"SELECT CateName FROM category Where CateID='"+CateID+"'" ;
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    cateName = reader[0].ToString();
                }
                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return cateName;

            }
            catch (Exception ex)
            {
                if (reader != null) reader.Close();
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return cateName;
            }
        }

        public CategoryDTO GetCateByID(string CateID)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;

            var cate = new CategoryDTO();
            try
            {        
                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                string strSQL = @"SELECT * FROM category Where CateID='" + CateID + "'";
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    cate = new CategoryDTO
                    {
                        CateID = reader[0].ToString(),
                        CateName = reader[1].ToString(),
                        CateParent = reader[2].ToString(),
                        Description = reader[3].ToString(),
                        VipLevel = int.Parse(reader[4].ToString()),
                        Active = reader[5].ToString() == "1",
                        Price = (double)reader[6],
                        Image = reader[7].ToString(),
                        Status = (int)reader[8],
                        CreatedDate = Convert.ToDateTime(reader[9]),
                        UpdatedDate = Convert.ToDateTime(reader[10])
                    };
                }
                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return cate;

            }
            catch (Exception ex)
            {
                if (reader != null) reader.Close();
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return cate;
            }
        }


        public List<CategoryDTO> GetListCategoryByPage(int offsetFirstRow,int numPerpage)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;
            var lstCategory = new List<CategoryDTO>();
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };
                
                string strSQL = @"SELECT * FROM category ORDER BY CreatedDate DESC LIMIT " + offsetFirstRow + "," + numPerpage;
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    try
                    {
                        var cate = new CategoryDTO
                        {
                            CateID = reader[0].ToString(),
                            CateName = reader[1].ToString(),
                            CateParent = reader[2].ToString(),
                            Description = reader[3].ToString(),
                            VipLevel = int.Parse(reader[4].ToString()),
                            Active = reader[5].ToString() == "1",
                            Price = (double)reader[6],
                            Image = reader[7].ToString(),
                            Status = (int)reader[8],
                            CreatedDate = Convert.ToDateTime(reader[9]),
                            UpdatedDate = Convert.ToDateTime(reader[10])
                        };
                        lstCategory.Add(cate);

                    }
                    catch (Exception ex)
                    {

                    }


                }
                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return lstCategory;

            }
            catch (Exception ex)
            {
                if (reader != null) reader.Close();
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return lstCategory;
            }
        }

        public int GetNumCategory()
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;
            int toTal=0;
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                const string strSQL = @"SELECT Count(*) FROM category";
                MysqlCmd.CommandText = strSQL;

                reader = MysqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    toTal = int.Parse(reader[0].ToString());
                }
                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
               // return lstCategory;
                return toTal;
            }
            catch (Exception)
            {
               if (reader != null) 
                MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return toTal;
            }
        }

        public List<CategoryDTO> searchCategoryByName(string key)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            MySqlDataReader reader = null;
            var lstCategory = new List<CategoryDTO>();
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                var strSQL = @"SELECT * FROM category where category.CateName Like '%" + key + "%' Order by category.CateName asc";
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteReader();
                while (reader.Read())
                {
                    try
                    {
                        var cate = new CategoryDTO();

                        cate.CateID = reader[0].ToString();
                        cate.CateName = reader[1].ToString();
                        cate.CateParent = reader[2].ToString();
                        cate.Description = reader[3].ToString();
                        cate.VipLevel = int.Parse(reader[4].ToString());
                        cate.Active = reader[5].ToString() == "1";
                        cate.Image = reader[7].ToString();
                        cate.Status = Convert.ToInt32(reader[8].ToString());
                        cate.CreatedDate = Convert.ToDateTime(reader[9].ToString());
                        cate.UpdatedDate = Convert.ToDateTime(reader[10].ToString());

                        lstCategory.Add(cate);

                    }
                    catch (Exception ex)
                    {
                        Logger.WriteLog(LogLevel.ERROR, ex + "\n" + ex.StackTrace);
                    }


                }
                reader.Close();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return lstCategory;

            }
            catch (Exception ex)
            {
                if (reader != null) reader.Close();
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return lstCategory;
            }
        }

        public bool delete(string key)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            int reader = 0;
            try
            {

                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                //  var strSQL = @"SELECT a.OptionID,a.ProID,b.Name,b.Price,a.Quantity FROM CateDetailProduct AS a JOIN Productdetail AS b ON a.ProID = b.ProID Where OptionID='" + optionID + "'";
                //var strSQL = @"UPDATE `tientetuong`.`category` SET `Active` = b '0' WHERE `category`.`CateID` = '" + key + "'";
                var strSQL = @"Delete FROM `tientetuong`.`category` where `category`.`CateID` = '" + key + "'";
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteNonQuery();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return reader > 0;
            }
            catch (Exception ex)
            {
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return false;
            }
        }

        public bool update(CategoryDTO item)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            int reader = 0;
            try
            {
                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                //  var strSQL = @"SELECT a.OptionID,a.ProID,b.Name,b.Price,a.Quantity FROM CateDetailProduct AS a JOIN Productdetail AS b ON a.ProID = b.ProID Where OptionID='" + optionID + "'";
                var strSQL = @"UPDATE `category` SET  `CateName` =  '" + item.CateName + "', "
               + "`Description` =  '" + item.Description + "', "
               + "`Image` =  '" + item.Image + "', "
               + "`Price` =  '" + item.Price.ToString() + "',"
               + "`Active` =  b'" + (bool.Parse(item.Active.ToString()) ? "1" : "0") + "'"
               + " WHERE  `category`.`CateID` =  '" + item.CateID + "';";
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteNonQuery();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return reader > 0;
            }
            catch (Exception ex)
            {
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return false;
            }
        }

        public bool add(CategoryDTO item)
        {
            MySqlConnection SqlConnection = null;
            MySqlCommand MysqlCmd = null;
            int reader = 0;
            try
            {
                SqlConnection = new MySqlConnection(Global.ConnectionString);
                if (SqlConnection.State == ConnectionState.Closed)
                    SqlConnection.Open();
                MysqlCmd = new MySqlCommand { Connection = SqlConnection };

                //  var strSQL = @"SELECT a.OptionID,a.ProID,b.Name,b.Price,a.Quantity FROM CateDetailProduct AS a JOIN Productdetail AS b ON a.ProID = b.ProID Where OptionID='" + optionID + "'";
                var strSQL = @"Insert into `category` (`CateID`, `CateName`, `Description`, `VipLevel`, `Active`, `Image`, `Price`, `Status`)"
                    + " Values ('" + item.CateID + "', '" + item.CateName + "', '" + item.Description + "', '" + item.VipLevel + "', b'" + ((bool)item.Active ? "1" : "0") + "', '" + item.Image
                    + "', '" + item.Price + "', '" + item.Status + "');";
                MysqlCmd.CommandText = strSQL;
                reader = MysqlCmd.ExecuteNonQuery();
                MysqlCmd.Dispose();
                SqlConnection.Close();
                SqlConnection.Dispose();
                return reader > 0;
            }
            catch (Exception ex)
            {
                if (MysqlCmd != null)
                    MysqlCmd.Dispose();
                if (SqlConnection != null)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }
                return false;
            }
        }
	}
}
